# coding=utf8
from openpyxl import load_workbook

import sys
reload(sys)
sys.setdefaultencoding('utf-8')

def save_data(fname, all_sql):
    file_object = open(fname, 'w')
    file_object.writelines(all_sql)
    file_object.close()


def gen_data(fname):
    insert_sql = ""
    print 'start.......'
    # 打开excel数据文件
    wb = load_workbook(filename=fname)
    sheet_ranges = wb['sheet1']
    # 循环读取2到65534行
    for rownum in range(2, 500):
        print 'Row No.:', rownum
        datamonth = str(sheet_ranges.cell(row=rownum, column=1).value)
        province = str(sheet_ranges.cell(row=rownum, column=2).value)
        city = str(sheet_ranges.cell(row=rownum, column=3).value)
        town = str(sheet_ranges.cell(row=rownum, column=4).value)
        brand = str(sheet_ranges.cell(row=rownum, column=5).value)
        vehicle_model = str(sheet_ranges.cell(row=rownum, column=6).value)
        vehicle_series = str(sheet_ranges.cell(row=rownum, column=7).value)
        vehicle_name = str(sheet_ranges.cell(row=rownum, column=8).value)
        vehicle_body = str(sheet_ranges.cell(row=rownum, column=9).value)
        color = str(sheet_ranges.cell(row=rownum, column=10).value)
        displacement = str(sheet_ranges.cell(row=rownum, column=11).value)
        domestic = str(sheet_ranges.cell(row=rownum, column=12).value)
        country = str(sheet_ranges.cell(row=rownum, column=13).value)
        manufacturer = str(sheet_ranges.cell(row=rownum, column=14).value)
        birthyear = str(sheet_ranges.cell(row=rownum, column=15).value)
        gender = str(sheet_ranges.cell(row=rownum, column=16).value)
        usage = str(sheet_ranges.cell(row=rownum, column=17).value)
        ownership = str(sheet_ranges.cell(row=rownum, column=18).value)
        paytype = str(sheet_ranges.cell(row=rownum, column=19).value)
        number = str(sheet_ranges.cell(row=rownum, column=20).value)

        insert_sql = insert_sql + u"insert into cvm_gccyc_2015 (datamonth, province, city, " \
                     u"town, brand, vehicle_model, vehicle_series, vehicle_name, " \
                     u"vehicle_body, color, displacement, domestic, country, " \
                     u"manufacturer, birthyear, gender, usefor, ownership, paytype, number) " \
                     u"values('" + datamonth + u"', '" + province + u"', '" + city + u"', '" \
                     + town + u"', '" + brand + u"', '" + vehicle_model + u"', '" + vehicle_series + u"', '" \
                     + vehicle_name + u"', '" + vehicle_body + u"', '" + color + u"', '" + displacement + u"', '" \
                     + domestic + u"', '" + country + u"', '" + manufacturer + u"', '" + birthyear + u"', '" \
                     + gender + u"', '" + usage + u"', '" + ownership + u"', '" + paytype + u"', " + number + u");\n"
    # 保存sql到文件
    save_data('output.sql', insert_sql)
    print 'over.......'


if __name__ == '__main__':
    gen_data('gccycutf8.xlsx')
